const path = require('path');
const pool = require('../config/database');

class UserModel {
  /**
   * 查询用户
   * @param {string} username 
   * @returns 
   */
  static async findByUsername(username) {
    const sql = 'SELECT * FROM users WHERE username = ?';
    const [rows] = await pool.query(sql, [username]);

    console.log('sql', sql, username);
    
    return rows[0];
  }

  /**
   * 获取全部
   * @returns 
   */
  async getAllUsers(queryParams) {
    const current = Number(queryParams.current);
    const pageSize = Number(queryParams.pageSize);
    const { searchField, searchValue } = queryParams
    const offset = (current - 1) * pageSize;
    const prepare_list = [pageSize, offset];
    const prepare_count = [];

    let WHERE_FIELD = '';
    if (searchField && searchValue) {
      WHERE_FIELD = `
        WHERE ${searchField}=?
      `;
      prepare_list.unshift(searchValue);
      prepare_count.push(searchValue);
    }

    const sql_count = `SELECT COUNT(id) AS value FROM users ${WHERE_FIELD}`;
    const sql_list = `
      SELECT 
        active, age, avatar, fullname, gender, id, username, role
      FROM users
      ${WHERE_FIELD}
      LIMIT ? OFFSET ?`;

    const [[count]] = await pool.query(sql_count, prepare_count);
    const [data] = await pool.query(sql_list, prepare_list);

    return {
      data,
      current,
      pageSize,
      total: count.value
    };
  }

  /**
   * 添加用户
   * @param {object} user 
   * @returns 
   */
  async addUser(user) {
    const { username, fullname, age, gender, password, active, role, avatar } = user;
    const sql = `INSERT INTO users
      (username, fullname, age, gender, password, active, role, avatar)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)`;
    const prepare = [username, fullname, age, gender, password, active, role, avatar];
    const [result] = await pool.query(sql, prepare);

    return result.insertId;
  }

  /**
   * 更新用户
   * @param {number} id 
   * @param {object} user 
   * @returns 
   */
  async updateUser(id, user) {
    const { username, fullname, age, gender, role } = user;
    const sql = 'UPDATE users SET username=?, fullname=?, age=?, gender=?, role=? WHERE id=?';
    const [result] = await pool.query(
      sql,
      [username, fullname, age, gender, role, id]
    );

    console.log('model', result);
    return {
      affectedRows: result.affectedRows,
      userinfo: { id: parseInt(id), ...user }
    };
  }

  /**
   * 更新密码
   * @param {Object} user 
   * @returns 
   */
  async updatePassword(user) {
    const { password, userId } = user;
    const sql = 'UPDATE users SET password=? WHERE id=?';
    const [result] = await pool.query(sql, [password, userId]);

    return {
      affectedRows: result.affectedRows,
      userinfo: { id: parseInt(userId), ...user }
    };
  }

  /**
   * 删除
   * @param {number} id 
   * @returns 
   */
  async deleteUser(id) {
    const [result] = await pool.query('DELETE FROM users WHERE id=?', [id]);

    return result.affectedRows;
  }

  async editAvatar() {
    const sql = '';
    const prepare = [];

    try {
      const result = await pool.query(sql, prepare);
      console.log('更新成功:', result);
    } catch (err) {
      console.error('SQL错误:', err);
      console.error('完整SQL:', err.sql || err.sqlMessage);
      throw err;
    }
  }
};

module.exports = UserModel;